package kr.co.doriclassic.summary_java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Map;

import kr.co.doriclassic.mylibrary.DbUtil;
import kr.co.doriclassic.summary_java.dto.Department;
import kr.co.doriclassic.summary_java.dto.Employee;

public class EmployeeDao implements InterfaceDao<Employee> {
	private Connection connection;
	private PreparedStatement pstmt;
	
	public EmployeeDao(Connection connection) {
		this.connection = connection;
	}

	
	@Override
	public void insertItem(Employee item) throws SQLException {
		String sql = "insert into employee values(?,?,?,?,?,?)";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setInt(3, item.getTitle());
		pstmt.setInt(4, item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setInt(6, item.getDeptno());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);	
	}

	@Override
	public void deleteItembyNo(Employee item) throws SQLException {
		String sql = "delete from employee where empno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	@Override
	public void updateItembyNo(Employee item) throws SQLException {
		String sql = "update employee set empname=?, title=?, manager=?, salary=?, deptno=? where empno=?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getEmpName());
		pstmt.setInt(2, item.getTitle());
		pstmt.setInt(3, item.getManager());
		pstmt.setInt(4, item.getSalary());
		pstmt.setInt(5, item.getDeptno());
		pstmt.setInt(6, item.getEmpNo());
		
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	
/*	create view v_employee as
	select e.empno, e.empname, c.titlename, m.empname as manager, e.salary, d.deptname 
	from employee e, employee m, code_title c, department d 
	where e.manager=m.empno and e.deptno=d.deptno and e.title = c.titleno;*/
	@Override
	public ArrayList<Employee> listItems() throws SQLException {
		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select empno, empname, title, manager, salary, deptno from employee";
		//String sql = "select empno, empname, title, manager, salary, dno from v_employee";
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next()){
			list.add(new Employee(
					rs.getInt("empno"), 
					rs.getString("empname"), 
					rs.getInt("title"), 
					rs.getInt("manager"), 
					rs.getInt("salary"), 
					rs.getInt("deptno")));
	
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public Employee searchItem(Employee item) throws SQLException {
		Employee employee = null;
		String sql = "select empno, empname, title, manager, salary, deptno from employee where empno=?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			employee = new Employee();
			employee.setEmpNo(rs.getInt(1));
			employee.setEmpName(rs.getString(2));
			employee.setTitle(rs.getInt(3));
			employee.setManager(rs.getInt(4));
			employee.setSalary(rs.getInt(5));
			employee.setDeptno(rs.getInt(6));
		}
		return employee;
	}	
	
	@Override
	public ArrayList<Employee> searchList(String item) throws SQLException {
		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select distinct empno, empname, title, manager, salary, e.deptno "
				+ "from employee e, department d, code_title c "
				+ "where empno regexp ? or empname regexp ? "
				+ "or (c.titlename regexp ? and e.title=c.titleno)"
				+ "or (d.deptname regexp ? and e.deptno=d.deptno)"
				+ "or salary=?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item);
		pstmt.setString(2, item);
		pstmt.setString(3, item);
		pstmt.setString(4, item);
		pstmt.setInt(5, Integer.parseInt(item));
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			list.add(new Employee(
					rs.getInt("empno"), 
					rs.getString("empname"), 
					rs.getInt("title"), 
					rs.getInt("manager"), 
					rs.getInt("salary"), 
					rs.getInt("deptno")));
	
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}	
	
	public void prnItemList(Comparator<Employee> comparator) throws SQLException {
		ArrayList<Employee> list=listItems();
		if(comparator == null){
			Collections.sort(list);			
		}
		else{
			Collections.sort(list, comparator);
		}
		for(Employee emp : list){
			System.out.println(emp);
		}		
	}

	public HashMap<String, Integer> searchTitleComboBox() throws SQLException{
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		String sql = "select titlename, titleno from code_title";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			map.put(rs.getString("titlename"), rs.getInt("titleno"));
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return map;
	}
	
	public HashMap<String, Integer> searchDeptComboBox() throws SQLException{
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		String sql = "select deptname, deptno from department";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			map.put(rs.getString("deptname"), rs.getInt("deptno"));
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return map;
	}
	
	public HashMap<String, Integer> searchManagerComboBox() throws SQLException{
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		String sql = "select empname, empno from employee";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			map.put(rs.getString("empname"), rs.getInt("empno"));
		}
		
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return map;
	}
	
	public String selectedTitleByTitleNo(int titleNo) throws SQLException{
		String sql = "select titlename from code_title where titleno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, titleNo);
		ResultSet rs = pstmt.executeQuery();
		String titleName=null;
		if (rs.next())
			titleName = rs.getString("titlename");
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return titleName;
	}
	
	public String selectedNameByEmpNo(int manager) throws SQLException {
		String sql = "select empname from employee where empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, manager);
		ResultSet rs = pstmt.executeQuery();
		String managerName=null;
		if (rs.next()){
			managerName = rs.getString("empname");
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return managerName;
	}

	public String selectedDeptNameByDeptNo(int deptno) throws SQLException {
		String sql = "select deptname from department where deptno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, deptno);
		ResultSet rs = pstmt.executeQuery();
		String deptName = null;
		if (rs.next()) {
			deptName= rs.getString("deptname");
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return deptName;
	}	
}